# 检测模块 - 包含所有数据检测功能
from config import load_env_structure_tx, load_env_structure_setl, load_env_structure_mdtrt, load_env_tbl_name
from sqlalchemy import text
import datetime

# 加载表和字段名称
SETL_TBL_NAME, MDTRT_TBL_NAME, FEE_TBL_NAME, DX_TBL_NAME, TX_TBL_NAME = load_env_tbl_name()
(
    SETL_SETL_ID, SETL_MDTRT_ID, SETL_PSN_NO, SETL_HSP_ID, SETL_SETL_TIME,
    SETL_GNR_C, SETL_GNR_B, SETL_IN_DATE_CHECK, SETL_OUT_DATE_CHECK, SETL_VALIDFLAG) = load_env_structure_setl()
(
    MDTRT_MDTRT_ID, MDTRT_IN_OUT_ID, MDTRT_HSP_ID, MDTRT_HSP_NAME,
    MDTRT_HSP_LV, MDTRT_PSN_NO, MDTRT_CERTNO, MDTRT_CERT_TYPE,
    MDTRT_PSN_NAME, MDTRT_GEND0, MDTRT_BRDY, MDTRT_AGE,
    MDTRT_INSUTYPE0, MDTRT_IN_TIME, MDTRT_OUT_TIME,
    MDTRT_IN_DEPT, MDTRT_OUT_DEPT, MDTRT_VALIDFLAG,
    MDTRT_OUT_DX_CODE_CHECK, MDTRT_OUT_DX_NAME_CHECK,
    MDTRT_OUTP_DX_CHECK) = load_env_structure_mdtrt()
(TX_SETL_ID, TX_IS_MAIN, TX_TX_CODE, TX_TX_NAME, TX_TX_TIME, TX_VALIDFLAG) = load_env_structure_tx()


def format_dt(dt):
    if isinstance(dt, str):
        return dt
    if dt is None:
        return ""
    if hasattr(dt, 'strftime'):
        return dt.strftime('%Y-%m-%d %H:%M:%S')
    return str(dt)

def inspect_e01_surgery_main_count(engine, check_results, setl_count):
    """E01: 测试手术表中每个SETL_ID的主手术数量"""
    print('============ E01：测试手术表中每个SETL_ID的主手术数量 ============')
    
    # 如果TX_IS_MAIN或TX_TX_CODE不存在，则跳过检测
    if not TX_IS_MAIN or not TX_TX_CODE:
        print("TX_IS_MAIN或TX_TX_CODE未定义，跳过E01检测")
        return

    sql = f"""
    SELECT {TX_SETL_ID}, COUNT(DISTINCT CASE WHEN {TX_IS_MAIN}='1' THEN {TX_TX_CODE} ELSE NULL END) AS main_tx_count
    FROM {TX_TBL_NAME} tx
    WHERE EXISTS(
        SELECT *
        FROM {SETL_TBL_NAME} m
        WHERE m.{SETL_SETL_ID} = tx.{TX_SETL_ID}
        AND m.{SETL_VALIDFLAG} = '1'
    )
    GROUP BY {TX_SETL_ID}
    HAVING COUNT(DISTINCT CASE WHEN {TX_IS_MAIN}='1' THEN {TX_TX_CODE} ELSE NULL END) != 1
    """
    
    with engine.connect() as connection:
        result = connection.execute(text(sql))
        invalid_tx_setl_ids = result.fetchall()
    
    # if invalid_tx_setl_ids:
    #     print("手术表中每个SETL_ID的主手术数量不是1的:")
    #     for row in invalid_tx_setl_ids[:20]:
    #         print(f"SETL_ID: {row[0]}, 主手术数量: {row[1]}")
    
    # 将检测结果导入 dirty_data 表 (SETL_ID, INSPECT_ID, INSPECT_NAME, LV, INFO)
    sqlIns = f"""
    INSERT INTO DIRTY_DATA
    WITH T0 AS (
        SELECT {TX_SETL_ID}, COUNT(DISTINCT {TX_IS_MAIN}) AS main_tx_count
        FROM {TX_TBL_NAME} tx
        WHERE EXISTS(
            SELECT *
            FROM {SETL_TBL_NAME} m
            WHERE m.{SETL_SETL_ID} = tx.{TX_SETL_ID}
            AND m.{SETL_VALIDFLAG} = '1'
        )
        GROUP BY {TX_SETL_ID}
        HAVING COUNT(DISTINCT {TX_IS_MAIN}) != 1
    )
    SELECT
        {TX_SETL_ID},
        'E01',
        '手术表中IS_MAIN数量不是1',
        2,
        '主手术数量: ' || main_tx_count
    FROM T0
    """
    with engine.connect() as connection:
        result_ins = connection.execute(text(sqlIns))
        inserted_count = result_ins.rowcount if hasattr(result_ins, 'rowcount') else None
        print(f"E01，手术表中setl_id的主手术数量不是1的记录: {inserted_count}")
        print(f"占主单表总数量的比例：{inserted_count / setl_count * 100:.2f}%" if setl_count > 0 else "占主单表总数量的比例：0%")
        connection.commit()
    
    check_results.append({
        "inspect_id": "E01",
        "inspect_name": "手术表中每个SETL_ID的主手术数量不是1",
        "sql": sql,
        "msg": "主手术数量不是1的SETL_ID",
        "info": [{"SETL_ID": row[0], "主手术数量": row[1]} for row in invalid_tx_setl_ids[:20]],
        "count": inserted_count if inserted_count is not None else 0,
        "ocp": inserted_count / setl_count * 100 if setl_count > 0 else 0
    })
    
    print('============ E01：测试手术表中每个SETL_ID的主手术数量 FINISHED ============')

def inspect_e02_tx_time_vs_in_time(engine, check_results, setl_count):
    """E02: 检查同一个SETL_ID的TX_TIME是否早于就诊表的IN_TIME"""
    print('============ E02：检查同一个SETL_ID的TX_TIME是否早于就诊表的IN_TIME ============')
    
    # 如果TX_TX_TIME或MDTRT_IN_TIME不存在，则跳过检测
    if not TX_TX_TIME or not MDTRT_IN_TIME:
        print("TX_TX_TIME或MDTRT_IN_TIME未定义，跳过E02检测")
        return

    sql = f"""
    SELECT tx.{TX_SETL_ID}, tx.{TX_TX_TIME}, mdtrt.{MDTRT_IN_TIME}
    FROM {TX_TBL_NAME} tx
    JOIN {SETL_TBL_NAME} setl ON tx.{TX_SETL_ID} = setl.{SETL_SETL_ID}
    JOIN {MDTRT_TBL_NAME} mdtrt ON setl.{MDTRT_MDTRT_ID} = mdtrt.{MDTRT_MDTRT_ID}
    WHERE tx.{TX_TX_TIME} < mdtrt.{MDTRT_IN_TIME}
    AND tx.{TX_VALIDFLAG} = '1'
    AND setl.{SETL_VALIDFLAG} = '1'
    AND mdtrt.{MDTRT_VALIDFLAG} = '1'
    """

    with engine.connect() as connection:
        result = connection.execute(text(sql))
        invalid_rows = result.fetchmany(20)

    # if invalid_rows:
    #     print("发现TX_TIME早于IN_TIME的记录:")
    #     for row in invalid_rows[:20]:
    #         print(f"SETL_ID: {row[0]}, TX_TIME: {row[1]}, IN_TIME: {row[2]}")
    
    # 将检测结果导入 dirty_data 表 (SETL_ID, INSPECT_ID, INSPECT_NAME, LV, INFO)
    sqlIns = f"""
    INSERT INTO DIRTY_DATA
    WITH T0 AS (
        SELECT tx.{TX_SETL_ID}, tx.{TX_TX_TIME}, mdtrt.{MDTRT_IN_TIME}
        FROM {TX_TBL_NAME} tx
        JOIN {SETL_TBL_NAME} setl ON tx.{TX_SETL_ID} = setl.{SETL_SETL_ID}
        JOIN {MDTRT_TBL_NAME} mdtrt ON setl.{MDTRT_MDTRT_ID} = mdtrt.{MDTRT_MDTRT_ID}
        WHERE tx.{TX_TX_TIME} < mdtrt.{MDTRT_IN_TIME}
        AND tx.{TX_VALIDFLAG} = '1'
        AND setl.{SETL_VALIDFLAG} = '1'
        AND mdtrt.{MDTRT_VALIDFLAG} = '1'
    )
    SELECT
        {TX_SETL_ID},
        'E02',
        '手术表TX_TIME早于就诊表IN_TIME',
        2,
        'TX_TIME: ' || {TX_TX_TIME} || '; IN_TIME: ' || {MDTRT_IN_TIME}
    FROM T0
    """
    with engine.connect() as connection:
        result_ins = connection.execute(text(sqlIns))
        inserted_count = result_ins.rowcount if hasattr(result_ins, 'rowcount') else None
        print(f"E02，手术表中setl_id的TX_TIME早于IN_TIME的记录: {inserted_count}")
        print(f"占主单表总数量的比例：{inserted_count / setl_count * 100:.2f}%" if setl_count > 0 else "占主单表总数量的比例：0%")
        connection.commit()

    check_results.append({
        "inspect_id": "E02",
        "inspect_name": "TX_TIME早于IN_TIME",
        "sql": sql,
        "msg": "TX_TIME早于IN_TIME的SETL_ID",
        "info": [{"SETL_ID": row[0], "TX_TIME": format_dt(row[1]), "IN_TIME": format_dt(row[2])} for row in invalid_rows[:20]],
        "count": inserted_count if inserted_count is not None else 0,
        "ocp": inserted_count / setl_count * 100 if setl_count > 0 else 0
    })
    
    print('============ E02：检查同一个SETL_ID的TX_TIME是否早于就诊表的IN_TIME FINISHED ============')

def inspect_e03_tx_time_vs_dsch_time(engine, check_results, setl_count):
    """E03: 检查同一个SETL_ID的TX_TIME是否晚于就诊表的DSCH_TIME（出院时间）"""
    print('============ E03：检查同一个SETL_ID的TX_TIME是否晚于出院时间 ============')
    
    # 如果TX_TX_TIME或MDTRT_OUT_TIME不存在，则跳过检测
    if not TX_TX_TIME or not MDTRT_OUT_TIME:
        print("TX_TX_TIME或MDTRT_OUT_TIME未定义，跳过E03检测")
        return

    sql = f"""
    SELECT tx.{TX_SETL_ID}, tx.{TX_TX_TIME}, mdtrt.{MDTRT_OUT_TIME}
    FROM {TX_TBL_NAME} tx
    JOIN {SETL_TBL_NAME} setl ON tx.{TX_SETL_ID} = setl.{SETL_SETL_ID}
    JOIN {MDTRT_TBL_NAME} mdtrt ON setl.{MDTRT_MDTRT_ID} = mdtrt.{MDTRT_MDTRT_ID}
    WHERE tx.{TX_TX_TIME} > mdtrt.{MDTRT_OUT_TIME}
    AND tx.{TX_VALIDFLAG} = '1'
    AND setl.{SETL_VALIDFLAG} = '1'
    AND mdtrt.{MDTRT_VALIDFLAG} = '1'
    """

    with engine.connect() as connection:
        result = connection.execute(text(sql))
        invalid_rows = result.fetchmany(20)

    # if invalid_rows:
    #     print("发现TX_TIME晚于DSCH_TIME的记录:")
    #     for row in invalid_rows[:20]:
    #         print(f"SETL_ID: {row[0]}, TX_TIME: {row[1]}, DSCH_TIME: {row[2]}")
    
    # 将检测结果导入 dirty_data 表 (SETL_ID, INSPECT_ID, INSPECT_NAME, LV, INFO)
    sqlIns = f"""
    INSERT INTO DIRTY_DATA
    WITH T0 AS (
        SELECT tx.{TX_SETL_ID}, tx.{TX_TX_TIME}, mdtrt.{MDTRT_OUT_TIME}
        FROM {TX_TBL_NAME} tx
        JOIN {SETL_TBL_NAME} setl ON tx.{TX_SETL_ID} = setl.{SETL_SETL_ID}
        JOIN {MDTRT_TBL_NAME} mdtrt ON setl.{MDTRT_MDTRT_ID} = mdtrt.{MDTRT_MDTRT_ID}
        WHERE tx.{TX_TX_TIME} > mdtrt.{MDTRT_OUT_TIME}
        AND tx.{TX_VALIDFLAG} = '1'
        AND setl.{SETL_VALIDFLAG} = '1'
        AND mdtrt.{MDTRT_VALIDFLAG} = '1'
    )
    SELECT
        {TX_SETL_ID},
        'E03',
        '手术表TX_TIME晚于就诊表DSCH_TIME',
        2,
        'TX_TIME: ' || {TX_TX_TIME} || '; DSCH_TIME: ' || {MDTRT_OUT_TIME}
    FROM T0
    """
    with engine.connect() as connection:
        result_ins = connection.execute(text(sqlIns))
        inserted_count = result_ins.rowcount if hasattr(result_ins, 'rowcount') else None
        print(f"E03，手术表中setl_id的TX_TIME晚于DSCH_TIME的记录: {inserted_count}")
        print(f"占主单表总数量的比例：{inserted_count / setl_count * 100:.2f}%" if setl_count > 0 else "占主单表总数量的比例：0%")
        connection.commit()

    check_results.append({
        "inspect_id": "E03",
        "inspect_name": "TX_TIME晚于DSCH_TIME",
        "sql": sql,
        "msg": "TX_TIME晚于DSCH_TIME的SETL_ID",
        "info": [{"SETL_ID": row[0], "TX_TIME": format_dt(row[1]), "OUT_TIME": format_dt(row[2])} for row in invalid_rows[:20]],
        "count": inserted_count if inserted_count is not None else 0,
        "ocp": inserted_count / setl_count * 100 if setl_count > 0 else 0
    })

    print('============ E03：检查同一个SETL_ID的TX_TIME是否晚于出院时间 FINISHED ============')